﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using UpLoadDataOnTime.DataBaseUtil;
using UpLoadDataOnTime.Entity;

namespace UpLoadDataOnTime.Model
{
    class GetMZData
    {

        IDatabase db = DbFactory.CreateDb();

        //读取配置文件中的连接字符串（需添加引用System.Configuration）
        private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
        private static string connStrP = ConfigurationManager.ConnectionStrings["dbConnStrP"].ConnectionString;
        private static string connStrL = ConfigurationManager.ConnectionStrings["dbConnStrL"].ConnectionString;

        //门诊患者基本信息
        public List<MZPatientInfo> getMZPatientInfo(string BeginDate,string EndDate)
        {
            string sql = "SELECT  '43120477700' AS YLJGDM," +
                         " '09' AS GRBSLX," +
                         " PatientID AS GRBSH," +
                         " '01' AS ZJLX," +
                         " CASE WHEN PersonID = '' THEN '000000000000000000' " +
                         " ELSE ISNULL(PersonID,'000000000000000000') " +
                         " END AS ZJHM," +
                         " '云南省第三人民医院' AS YYMC," +
                         " CASE  WHEN Sex = '男' THEN '1'" +
                         " WHEN Sex = '女' THEN '2'" +
                         " END AS XB," +
                         " PatientName AS XM," +
                         " '90' AS HYZK," +
                         " CONVERT(VARCHAR(10), BirthDay, 120) AS CSRQ," +
                         " CASE Address WHEN '' THEN '-' ELSE Address END AS CSD," +
                         " '01' AS MZ," +
                         " '156' AS GJ," +
                         " CONVERT(VARCHAR(24), Tel) AS SJHM" +
                         " FROM dbo.PatientBase(NOLOCK)" +
                         " WHERE CreateDate >= @BeginDate AND CreateDate < @EndDate" +
                         " ORDER BY CreateDate";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            List<MZPatientInfo> list = db.Context.QueryMany<MZPatientInfo>(connStr, sql, parameters);
            return list;
        }

        //门诊挂号信息
        public List<MZTbResistration> GetMZTbResistrations(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                                    " +
                         "       MZNum AS GHJLID,                                            " +
                         "       '云南省第三人民医院' AS YYMC,                                " +
                         "       '09' AS GRBSLX,                                             " +
                         "       PatientID AS GRBSH,                                         " +
                         "       FPNum AS JZLSH,                                             " +
                         "       CASE WHEN XMAttrib = 1 THEN '0'                             " +
                         "            WHEN XMAttrib = 2 THEN '1'                             " +
                         "            WHEN XMAttrib = 3 THEN '0'                             " +
                         "       ELSE '0'                                                    " +
                         "       END AS SFJZ,                                                " +
                         "       CASE WHEN DelDate IS NULL THEN '01'                         " +
                         "       ELSE '02'                                                   " +
                         "       END AS GTHBZ,                                               " +
                         "       CONVERT(VARCHAR(10),RegisterDate,121) AS GTHSJ,             " +
                         "       CASE WHEN XMAttrib = 1 THEN '100'                           " +
                         "            WHEN XMAttrib = 2 THEN '200'                           " +
                         "            WHEN XMAttrib = 3 THEN '108'                           " +
                         "       ELSE '999'                                                  " +
                         "       END AS  GHLB,                                               " +
                         "       CASE WHEN ReserveFlag = 1 THEN '02'                         " +
                         "       ELSE '01'                                                   " +
                         "       END AS GHTJBM,                                              " +
                         "       CASE WHEN TreatmentCode = '1' THEN '01'                     " +
                         "            WHEN TreatmentCode = '2' THEN '02'                     " +
                         "            WHEN TreatmentCode = '3' THEN '03'                     " +
                         "            WHEN TreatmentCode = '4' THEN '04'                     " +
                         "            WHEN TreatmentCode = '5' THEN '05'                     " +
                         "            WHEN TreatmentCode = '6' THEN '06'                     " +
                         "            WHEN TreatmentCode = '7' THEN '07'                     " +
                         "            WHEN TreatmentCode = '8' THEN '08'                     " +
                         "            WHEN TreatmentCode = '9' THEN '99'                     " +
                         "       ELSE '99'                                                   " +
                         "       END AS BXLX,                                                " +
                         "       KSCode AS KSBM,                                             " +
                         "       KSName AS KSMC,                                             " +
                         "       CASE WHEN YSCode = '' THEN '-'                           " +
                         "       ELSE YSCode                                                 " +
                         "       END AS GHYSBH,                                              " +
                         "       CASE WHEN YSName = '' THEN '-'                           " +
                         "       ELSE YSName                                                 " +
                         "       END AS GHYSXM,                                              " +
                         "       '5' AS WDBZ,                                                " +
                         "       XMMoney AS GHZFY                                            " +
                         "FROM MZRegisterSheet(NOLOCK)                                       " +
                         "WHERE RegisterDate >= @BeginDate AND RegisterDate < @EndDate " +
                         "ORDER BY RegisterDate";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            Console.WriteLine(db.Context.QueryMany<MZTbResistration>(connStr, sql, parameters));
            return db.Context.QueryMany<MZTbResistration>(connStr, sql, parameters);
        }

        //门诊就诊信息
        public List<MZTbClinicRecord> GetMZTbClinicRecords(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                                                   " +
                         "       CONVERT(VARCHAR(36),b.KeyNo) AS JZMXID,                                    " +
                         "       '09' AS GRBSLX,                                                            " +
                         "       PatientID AS GRBSH,                                                        " +
                         "       '云南省第三人民医院' AS YYMC,                                                 " +
                         "       a.MZnum AS JZLSH,                                                          " +
                         "       CASE WHEN RepeatFlag IS NULL THEN '0'                                      " +
                         "            WHEN RepeatFlag = 1 THEN '1'                                          " +
                         "       END AS SFFZ,                                                               " +
                         "       '-' AS LCYXLXDM,                                                          " +
                         "       CASE WHEN TreatmentCode = '1' THEN '01'                                    " +
                         "            WHEN TreatmentCode = '2' THEN '02'                                    " +
                         "            WHEN TreatmentCode = '3' THEN '03'                                    " +
                         "            WHEN TreatmentCode = '4' THEN '04'                                    " +
                         "            WHEN TreatmentCode = '5' THEN '05'                                    " +
                         "            WHEN TreatmentCode = '6' THEN '06'                                    " +
                         "            WHEN TreatmentCode = '7' THEN '07'                                    " +
                         "            WHEN TreatmentCode = '8' THEN '08'                                    " +
                         "            WHEN TreatmentCode = '9' THEN '99'                                    " +
                         "       ELSE '99'                                                                  " +
                         "       END AS HZSX,                                                               " +
                         "       CASE WHEN XMAttrib = 1 THEN '100'                                          " +
                         "            WHEN XMAttrib = 2 THEN '200'                                          " +
                         "            WHEN XMAttrib = 3 THEN '108'                                          " +
                         "       ELSE '999'                                                                 " +
                         "       END AS JZLX,                                                               " +
                         "       KSCode AS JZKSBM,                                                          " +
                         "       KSName AS JZKSMC,                                                          " +
                         "       ISNULL(CONVERT(VARCHAR(10),ProcessDate,121),'0000-00-00') AS JZKSRQ,       " +
                         "       CASE YSCode WHEN '' THEN '-' ELSE a.YSCode END AS ZZYSBH,                  " +
                         "       CASE YSName WHEN '' THEN '-' ELSE a.YSName END AS ZZYSXM,                  " +
                         "       CASE DiagCode WHEN '' THEN '-' ELSE b.DiagCode END AS MZZDDM,              " +
                         "       Diagnose AS MZZDMC,                                                        " +
                         "       CASE a.Advice WHEN '' THEN '-' ELSE a.Advice END AS JZZDSM,                " +
                         "       CASE Complaint WHEN '' THEN '无' ELSE ISNULL(Complaint,'无') END AS ZS     " +
                         "FROM MZRegisterSheet a(NOLOCK) JOIN dbo.MZDiagnose b(NOLOCK) ON a.MZNum = b.MZNum " +
                         "WHERE a.RegisterDate >= @BeginDate AND a.RegisterDate < @EndDate            " +
                         "ORDER BY a.RegisterDate ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbClinicRecord>(connStr, sql, parameters);
        }

        //门诊处方信息
        public List<MZTbRecipe> GetMZTbRecipes(string BeginDate,string EndDate)
        {
            string sql = "SELECT  '43120477700' AS YLJGDM ,                                                                                     " +
                         "            CONVERT(VARCHAR(20),ROW_NUMBER() over(order by a.KEYNO)) + CONVERT(VARCHAR(20),a.CFNUM) + 'ZS' AS CFZID , " +
                         "            '云南省第三人民医院' AS YYMC,                                                                                " +
                         "            b.MZNUM AS JZLSH ,                                                                                        " +
                         "            '09' AS GRBSLX ,                                                                                          " +
                         "            PATIENTID AS GRBSH ,                                                                                      " +
                         "			b.CFNUM AS CFHM ,                                                                                          " +
                         "            CASE WHEN b.CFFLAG = 1 THEN '0101'                                                                        " +
                         "                 WHEN b.CFFLAG = 2 THEN '0103'                                                                        " +
                         "            ELSE '99'                                                                                                 " +
                         "            END AS CFDL ,                                                                                             " +
                         "            CASE WHEN CFTYPE = NULL THEN '01'                                                                         " +
                         "                 WHEN CFTYPE = 1 THEN '02'                                                                            " +
                         "                 WHEN CFTYPE = 2 THEN '03'                                                                            " +
                         "                 WHEN CFTYPE = 3 THEN '05'                                                                            " +
                         "                 WHEN CFTYPE = 4 THEN '04'                                                                            " +
                         "                 WHEN CFTYPE = 5 THEN '99'                                                                            " +
                         "                 WHEN CFTYPE = 6 THEN '99'                                                                            " +
                         "                 WHEN CFTYPE = 7 THEN '04'                                                                            " +
                         "            ELSE '99'                                                                                                 " +
                         "            END AS CFLX ,                                                                                             " +
                         "			  CONVERT(VARCHAR(10),CFDATE,121) AS KFSJ ,                                                                 " +
                         "            a.YSCODE AS KFYSBH ,                                                                                      " +
                         "            a.YSNAME AS KFYSXM ,                                                                                      " +
                         "            b.YSKSCODE AS KFKSDM ,                                                                                    " +
                         "            CONVERT(VARCHAR(76),b.YSKSNAME) AS KFKSMC ,                                                               " +
                         "            CASE ISNULL(DiagCode,'-') WHEN '' THEN '-' ELSE ISNULL(DiagCode,'-') END AS XYZDDM ,                      " +
                         "            ISNULL(DiagName,'-') AS XYZDMC ,                                                                          " +
                         "            '-' AS ZYBMDM ,                                                                                           " +
                         "            '-' AS ZYBMMC ,                                                                                           " +
                         "            '-' AS ZYZHDM ,                                                                                           " +
                         "            '-' AS ZYZHMC ,                                                                                           " +
                         "            c.CFMONEY AS CFJE                                                                                         " +
                         "FROM dbo.MZCFYPK a(NOLOCK)                                                                                            " +
                         "         JOIN MZCFINF_YZ b(NOLOCK) ON b.FPNUM = a.FPNum                                                               " +
                         "         JOIN dbo.MZCFINF c(NOLOCK) ON c.CFNUM = a.CFNUM                                                              " +
                         "         LEFT JOIN dbo.MZDiagnose d(NOLOCK) ON d.MZNum = b.MZNum                                                      " +
                         "WHERE b.CFDATE >= @BeginDate AND b.CFDATE < @EndDate                                                                  " +
                         "UNION ALL                                                                                                             " +
                         "SELECT  '43120477700' AS YLJGDM ,                                                                                     " +
                         "            CONVERT(VARCHAR(20),ROW_NUMBER() over(order by a.KEYNO)) + CONVERT(VARCHAR(20),a.CFNUM) + 'ZS' AS CFZID , " +
                         "            '云南省第三人民医院' AS YYMC,                                                                               " + 
                         "            b.MZNUM AS JZLSH ,                                                                                        " +
                         "            '09' AS GRBSLX ,                                                                                          " +
                         "            PATIENTID AS GRBSH ,                                                                                      " +
                         "			b.CFNUM AS CFHM ,                                                                                           " +
                         "            CASE WHEN b.CFFLAG = 1 THEN '0101'                                                                        " +
                         "                 WHEN b.CFFLAG = 2 THEN '0103'                                                                        " +
                         "            ELSE '99'                                                                                                 " +
                         "            END AS CFDL ,                                                                                             " +
                         "            CASE WHEN CFTYPE = NULL THEN '01'                                                                         " +
                         "                 WHEN CFTYPE = 1 THEN '02'                                                                            " +
                         "                 WHEN CFTYPE = 2 THEN '03'                                                                            " +
                         "                 WHEN CFTYPE = 3 THEN '05'                                                                            " +
                         "                 WHEN CFTYPE = 4 THEN '04'                                                                            " +
                         "                 WHEN CFTYPE = 5 THEN '99'                                                                            " +
                         "                 WHEN CFTYPE = 6 THEN '99'                                                                            " +
                         "                 WHEN CFTYPE = 7 THEN '04'                                                                            " +
                         "            ELSE '99'                                                                                                 " +
                         "            END AS CFLX ,                                                                                             " +
                         "			  CONVERT(VARCHAR(10),CFDATE,121) AS KFSJ ,                                                                 " +
                         "            a.YSCODE AS KFYSBH ,                                                                                      " +
                         "            a.YSNAME AS KFYSXM ,                                                                                      " +
                         "            b.YSKSCODE AS KFKSDM ,                                                                                    " +
                         "            CONVERT(VARCHAR(76),b.YSKSNAME) AS KFKSMC ,                                                               " +
                         "            CASE ISNULL(DiagCode,'-') WHEN '' THEN '-' ELSE ISNULL(DiagCode,'-') END AS XYZDDM ,                      " +
                         "            ISNULL(DiagName,'-') AS XYZDMC ,                                                                          " +
                         "            '-' AS ZYBMDM ,                                                                                           " +
                         "            '-' AS ZYBMMC ,                                                                                           " +
                         "            '-' AS ZYZHDM ,                                                                                           " +
                         "            '-' AS ZYZHMC ,                                                                                           " +
                         "            c.CFMONEY AS CFJE                                                                                         " +
                         "FROM dbo.MZCFYPKHIS a(NOLOCK)                                                                                         " +
                         "         JOIN MZCFINF_YZ b(NOLOCK) ON b.FPNUM = a.FPNum                                                               " +
                         "         JOIN dbo.MZCFINFHIS c(NOLOCK) ON c.CFNUM = a.CFNUM                                                           " +
                         "         LEFT JOIN dbo.MZDiagnose d(NOLOCK) ON d.MZNum = b.MZNum                                                      " +
                         "WHERE b.CFDATE >= @BeginDate AND b.CFDATE < @EndDate                                                                  ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbRecipe>(connStr, sql, parameters);
        }

        //门诊处方药品信息 
        public List<MZTbMedicine> GetMZTbMedicines(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                                                                                                                                                        " +
"                                CONVERT(VARCHAR(20),ROW_NUMBER() over(order by a.KEYNO)) + CONVERT(VARCHAR(20),a.KEYNO) + 'ZS' AS CFMXID,                                              " +
"                                '云南省第三人民医院' AS YYMC,                                                                                                                          " +
"                                MZNUM AS JZLSH,                                                                                                                                        " +
"                                '09' AS GRBSLX,                                                                                                                                        " +
"                                PatientID AS GRBSH,                                                                                                                                    " +
"                                a.CFNUM AS CFZID,                                                                                                                                      " +
"                                a.YZID AS CFYZZH,                                                                                                                                      " +
"                                a.KMCODE AS ZLXMLBBM,                                                                                                                                  " +
"                                c.XMFLAG AS XMFLBM,                                                                                                                                    " +
"                                a.KMNAME AS XMFLMC,                                                                                                                                    " +
"                                '99' AS JXDM,                                                                                                                                          " +
"                                a.GOODSNAME AS YPGG,                                                                                                                                   " +
"                                CASE a.YPPath  WHEN '外阴道给药' THEN '605'                                                                                                            " +
"											   WHEN '皮试' THEN '401'                                                                                                                   " +
"											   WHEN '塞肛' THEN '2'                                                                                                                     " +
"											   WHEN '喷雾' THEN '609'                                                                                                                   " +
"											   WHEN '清洗' THEN '9'                                                                                                                     " +
"											   WHEN '吸入' THEN '606'                                                                                                                   " +
"											   WHEN '漱口' THEN '9'                                                                                                                     " +
"											   WHEN '滴鼻' THEN '608'                                                                                                                   " +
"											   WHEN '舌下含服' THEN '3'                                                                                                                 " +
"											   WHEN '雾化吸入' THEN '5'                                                                                                                 " +
"											   WHEN '其他' THEN '9'                                                                                                                     " +
"											   WHEN NULL THEN '9'                                                                                                                       " +
"											   WHEN '外涂' THEN '612'                                                                                                                   " +
"											   WHEN '滴耳' THEN '699'                                                                                                                   " +
"											   WHEN '口服' THEN '1'                                                                                                                     " +
"											   WHEN '涂眼' THEN '699'                                                                                                                   " +
"											   WHEN '静推' THEN '404'                                                                                                                   " +
"											   WHEN '局麻' THEN '699'                                                                                                                   " +
"											   WHEN '静滴' THEN '404'                                                                                                                   " +
"											   WHEN '滴眼' THEN '607'                                                                                                                   " +
"											   WHEN '灌肠用' THEN '604'                                                                                                                 " +
"											   WHEN '皮下注射' THEN '401'                                                                                                               " +
"											   WHEN '肌肉注射' THEN '403'                                                                                                               " +
"											   WHEN '外敷' THEN '611'                                                                                                                   " +
"											   ELSE '9'                                                                                                                                 " +
"											   END AS YYTJDM,                                                                                                                           " +
"                                CASE WHEN YZUSEDMETHOD = 'bid' THEN '01'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'biw' THEN '02'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Hs'  THEN '03'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q12h' THEN '04'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'q1h' THEN '05'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q3h' THEN '06'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q6h' THEN '07'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q8h' THEN '08'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'qd' THEN '09'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'qid' THEN '10'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'qod' THEN '11'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'qw' THEN '12'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'st' THEN '13'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'TID' THEN '14'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q30D' THEN '15'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'Q2W' THEN '16'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'TIW' THEN '17'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q30M' THEN '18'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'Q2H' THEN '19'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q4H' THEN '20'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q5H' THEN '21'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q12H' THEN '22'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'Q72H' THEN '23'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'QM' THEN '24'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'QN' THEN '25'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'QON' THEN '26'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q5D' THEN '27'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q10D' THEN '28'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'C12H' THEN '29'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'C24H' THEN '30'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'PRN' THEN '31'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'AC' THEN '32'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'AM' THEN '33'                                                                                                                " +
"                                     ELSE '99'                                                                                                                                         " +
"                                END AS SYPCDM,                                                                                                                                         " +
"                                YZUSEDMETHOD AS YYPC,                                                                                                                                  " +
"                                YPJL AS SYCJL,                                                                                                                                         " +
"                                CONVERT(VARCHAR(6),YPJLUNIT) AS SYJLDW,                                                                                                                " +
"                                '0.00' AS SYZJL ,                                                                                                                                         " +
"                                '-' AS ZYYYFF,                                                                                                                                         " +
"                                a.CFCOUNT AS FYSL,                                                                                                                                     " +
"                                a.UNITNAME AS FYSLDW,                                                                                                                                  " +
"                                '0.00' AS YYTS,                                                                                                                                           " +
"                         	     '-' AS YWLX,                                                                                                                                           " +
"                                CONVERT(VARCHAR(10),a.FYDATE,120) AS CFKSSJ,                                                                                                           " +
"                                '0000-00-00' AS CFTZSJ                                                                                                                                          " +
"                         FROM MZCFYPK a(NOLOCK)                                                                                                                                        " +
"                              JOIN dbo.MZCFINF b(NOLOCK) ON a.CFNUM = b.CFNUM                                                                                                          " +
"                              JOIN dbo.MZCFYPK_YZ c(NOLOCK) ON a.CFNUM = c.CFNUM                                                                                                       " +
"                         WHERE  a.FYDATE >= @BeginDate AND a.FYDATE < @EndDate                                                                                                   " +
"                         UNION ALL                                                                                                                                                     " +
"                          SELECT '43120477700' AS YLJGDM,                                                                                                                              " +
"                                CONVERT(VARCHAR(20),ROW_NUMBER() over(order by a.KEYNO)) + CONVERT(VARCHAR(20),a.KEYNO) + 'ZS' AS CFMXID,                                              " +
"                                '云南省第三人民医院' AS YYMC,                                                                                                                          " +
"                                MZNUM AS JZLSH,                                                                                                                                        " +
"                                '09' AS GRBSLX,                                                                                                                                        " +
"                                PatientID AS GRBSH,                                                                                                                                    " +
"                                a.CFNUM AS CFZID,                                                                                                                                      " +
"                                a.YZID AS CFYZZH,                                                                                                                                      " +
"                                a.KMCODE AS ZLXMLBBM,                                                                                                                                  " +
"                                c.XMFLAG AS XMFLBM,                                                                                                                                    " +
"                                a.KMNAME AS XMFLMC,                                                                                                                                    " +
"                                '99' AS JXDM,                                                                                                                                          " +
"                                a.GOODSNAME AS YPGG,                                                                                                                                   " +
"                                CASE a.YPPath  WHEN '外阴道给药' THEN '605'                                                                                                            " +
"											   WHEN '皮试' THEN '401'                                                                                                                   " +
"											   WHEN '塞肛' THEN '2'                                                                                                                     " +
"											   WHEN '喷雾' THEN '609'                                                                                                                   " +
"											   WHEN '清洗' THEN '9'                                                                                                                     " +
"											   WHEN '吸入' THEN '606'                                                                                                                   " +
"											   WHEN '漱口' THEN '9'                                                                                                                     " +
"											   WHEN '滴鼻' THEN '608'                                                                                                                   " +
"											   WHEN '舌下含服' THEN '3'                                                                                                                 " +
"											   WHEN '雾化吸入' THEN '5'                                                                                                                 " +
"											   WHEN '其他' THEN '9'                                                                                                                     " +
"											   WHEN NULL THEN '9'                                                                                                                       " +
"											   WHEN '外涂' THEN '612'                                                                                                                   " +
"											   WHEN '滴耳' THEN '699'                                                                                                                   " +
"											   WHEN '口服' THEN '1'                                                                                                                     " +
"											   WHEN '涂眼' THEN '699'                                                                                                                   " +
"											   WHEN '静推' THEN '404'                                                                                                                   " +
"											   WHEN '局麻' THEN '699'                                                                                                                   " +
"											   WHEN '静滴' THEN '404'                                                                                                                   " +
"											   WHEN '滴眼' THEN '607'                                                                                                                   " +
"											   WHEN '灌肠用' THEN '604'                                                                                                                 " +
"											   WHEN '皮下注射' THEN '401'                                                                                                               " +
"											   WHEN '肌肉注射' THEN '403'                                                                                                               " +
"											   WHEN '外敷' THEN '611'                                                                                                                   " +
"											   ELSE '9'                                                                                                                                 " +
"											   END AS YYTJDM,                                                                                                                           " +
"                                CASE WHEN YZUSEDMETHOD = 'bid' THEN '01'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'biw' THEN '02'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Hs'  THEN '03'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q12h' THEN '04'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'q1h' THEN '05'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q3h' THEN '06'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q6h' THEN '07'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'q8h' THEN '08'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'qd' THEN '09'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'qid' THEN '10'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'qod' THEN '11'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'qw' THEN '12'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'st' THEN '13'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'TID' THEN '14'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q30D' THEN '15'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'Q2W' THEN '16'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'TIW' THEN '17'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q30M' THEN '18'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'Q2H' THEN '19'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q4H' THEN '20'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q5H' THEN '21'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q12H' THEN '22'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'Q72H' THEN '23'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'QM' THEN '24'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'QN' THEN '25'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'QON' THEN '26'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q5D' THEN '27'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'Q10D' THEN '28'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'C12H' THEN '29'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'C24H' THEN '30'                                                                                                              " +
"                                     WHEN YZUSEDMETHOD = 'PRN' THEN '31'                                                                                                               " +
"                                     WHEN YZUSEDMETHOD = 'AC' THEN '32'                                                                                                                " +
"                                     WHEN YZUSEDMETHOD = 'AM' THEN '33'                                                                                                                " +
"                                     ELSE '99'                                                                                                                                         " +
"                                END AS SYPCDM,                                                                                                                                         " +
"                                YZUSEDMETHOD AS YYPC,                                                                                                                                  " +
"                                YPJL AS SYCJL,                                                                                                                                         " +
"                                CONVERT(VARCHAR(6),YPJLUNIT) AS SYJLDW,                                                                                                                " +
"                                '0.00' AS SYZJL ,                                                                                                                                         " +
"                                '-' AS ZYYYFF,                                                                                                                                         " +
"                                a.CFCOUNT AS FYSL,                                                                                                                                     " +
"                                a.UNITNAME AS FYSLDW,                                                                                                                                  " +
"                                '0.00' AS YYTS,                                                                                                                                           " +
"                         	     '-' AS YWLX,                                                                                                                                           " +
"                                CONVERT(VARCHAR(10),a.FYDATE,120) AS CFKSSJ,                                                                                                           " +
"                                '0000-00-00' AS CFTZSJ                                                                                                                                          " +
"                         FROM dbo.MZCFYPKHIS a(NOLOCK)                                                                                                                                 " +
"                              JOIN dbo.MZCFINFHIS b(NOLOCK) ON a.CFNUM = b.CFNUM                                                                                                       " +
"                              JOIN dbo.MZCFYPK_YZ c(NOLOCK) ON a.CFNUM = c.CFNUM                                                                                                       " +
"                         WHERE  a.FYDATE >= @BeginDate AND a.FYDATE < @EndDate                                                                                                    ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbMedicine>(connStr, sql, parameters);
        }

        //门诊手术信息
        public List<MZTbOperation> GetMZTbOperations(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM ,                         " +
                         "       a.KEYNO AS SSMXLSH,                               " +
                         "       '云南省第三人民医院' AS YYMC,                       " +
                         "       b.MZNUM AS JZLSH,                                 " +
                         "       '09' AS GRBSLX,                                   " +
                         "       PATIENTID AS GRBSH,                               " +
                         "       '1' AS  MZZYBZ,                                   " +
                         "       '9' AS SSLX,                                      " +
                         "CASE c.DiagCode WHEN '' THEN '-' ELSE c.DiagCode END AS SSCZDM, " +
                         "       CHECKNAME AS SSCZMC,                              " +
                         "       c.DiagName AS SSQZD,                              " +
                         "       c.DiagName AS SSHZD,                              " +
                         "       CONVERT(VARCHAR(10),a.FPDATE,121) AS SSKSSJ,      " +
                         "       CONVERT(varchar(10),GETDATE(),120) AS SSJSSJ,     " +
                         "       a.YSCODE AS SSYSBH,                               " +
                         "       a.YSNAME AS SSYSXM,                               " +
                         "       '4' AS QKYHDJ                                     " +
                         "FROM dbo.MZCHECK a(NOLOCK)                               " +
                         "JOIN dbo.MZINVOICE b(NOLOCK)                             " +
                         "ON b.FPNum = a.FPNUM                                     " +
                         "JOIN dbo.MZDiagnose c(NOLOCK)                            " +
                         "ON c.MZNum = b.MZNum                                     " +
                         "WHERE KMCODE = 'G' AND a.DELDATE IS NULL                 " +
                         "AND a.FPDATE >= @BeginDate AND a.FPDATE < @EndDate       " +
                         "AND b.CDNUM IS NULL                                      " +
                         "UNION ALL                                                " +
                         "SELECT '43120477700' AS YLJGDM ,                         " +
                         "       a.KEYNO AS SSMXLSH,                               " +
                         "       '云南省第三人民医院' AS YYMC,                       " +
                         "       b.MZNUM AS JZLSH,                                 " +
                         "       '09' AS GRBSLX,                                   " +
                         "       PATIENTID AS GRBSH,                               " +
                         "       '1' AS  MZZYBZ,                                   " +
                         "       '9' AS SSLX,                                      " +
                         "CASE c.DiagCode WHEN '' THEN '-' ELSE c.DiagCode END AS SSCZDM, " +
                         "       CHECKNAME AS SSCZMC,                              " +
                         "       c.DiagName AS SSQZD,                              " +
                         "       c.DiagName AS SSHZD,                              " +
                         "       CONVERT(VARCHAR(10),a.FPDATE,121) AS SSKSSJ,      " +
                         "       CONVERT(varchar(10),GETDATE(),120) AS SSJSSJ,     " +
                         "       a.YSCODE AS SSYSBH,                               " +
                         "       a.YSNAME AS SSYSXM,                               " +
                         "       '4' AS QKYHDJ                                     " +
                         "FROM dbo.MZCHECKHIS a(NOLOCK)                            " +
                         "JOIN dbo.MZINVOICEHIS b(NOLOCK)                          " +
                         "ON b.FPNum = a.FPNUM                                     " +
                         "JOIN dbo.MZDiagnose c(NOLOCK)                            " +
                         "ON c.MZNum = b.MZNum                                     " +
                         "WHERE KMCODE = 'G' AND a.DELDATE IS NULL                 " +
                         "AND a.FPDATE >= @BeginDate AND a.FPDATE < @EndDate       " +
                         "AND b.CDNUM IS NULL ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbOperation>(connStr, sql, parameters);
        }

        //门诊检查信息
        public List<MZTbCheckRecord> GetMZTbCheckRecords(string BeginDate,string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                              " +
                         "       '云南省第三人民医院' AS YYMC,                          " +
                         "       AccessionNumber AS JCBGID,                            " +
                         "       OutPatientNum AS JZLSH,                               " +
                         "       '09' AS GRBSLX,                                       " +
                         "       PatientID_His AS GRBSH,                               " +
                         "       '1' AS MZZYBZ,                                        " +
                         "       CONVERT(VARCHAR(20),PatientStudyDate,120) AS JCRQ,    " +
                         "       ReportDoctor AS JCYSQM,                               " +
                         "       ClinicName AS BGJGMC,                                 " +
                         "       ClinicName AS BGKSMC,                                 " +
                         "       ReportDoctor AS BGYSQM,                               " +
                         "       ISNULL(DiagnosesText,'未上传') AS JCBGJGKGSJ,         " +
                         "       ISNULL(DiagnosesText,'未上传') AS JCBGJGZGTS,         " +
                         "       CASE ModalityType WHEN 'DR' THEN '04'                 " +
                         "                         WHEN 'CT' THEN '01'                 " +
                         "                         WHEN 'B超' THEN '06'                " +
                         "                         WHEN '1胃肠镜' THEN '08'            " +
                         "                         WHEN '5胃肠镜' THEN '08'            " +
                         "                         WHEN 'B超-JR' THEN '06'             " +
                         "                         WHEN 'MG' THEN '10'                 " +
                         "                         WHEN 'MR' THEN '02'                 " +
                         "                         WHEN 'OT' THEN '10'                 " +
                         "                         WHEN 'US' THEN '06'                 " +
                         "                         WHEN '病理' THEN '07'               " +
                         "                         WHEN '肠镜' THEN '08'               " +
                         "                         WHEN '电子气管镜' THEN '08'         " +
                         "                         WHEN '胃肠镜' THEN '08'             " +
                         "       ELSE '10'                                             " +
                         "       END AS JCLX,                                          " +
                         "       Modality AS YQBM,                                     " +
                         "       ClinicName AS JCKSMC,                                 " +
                         "       CASE ScanWay WHEN '' THEN '-' ELSE ScanWay END AS JCBW, " +
                         "       BodyPartExamined AS JCMC,                             " +
                         "       CASE WHEN Abnormality IS NULL THEN 2                  " +
                         "            WHEN Abnormality = 2 THEN 2                      " +
                         "            WHEN Abnormality = 3 THEN 3                      " +
                         "       ELSE Abnormality                                      " +
                         "       END AS YYS,                                           " +
                         "       DiagnosesText AS BGLCZD,                              " +
                         "       DiagnosesText AS YXBX,                                " +
                         "       DiagnosesText AS YXZD,                                " +
                         "       '1' AS SFYYY                                          " +
                         "FROM PatientSchedule a(NOLOCK)                               " +
                         "JOIN dbo.PatientBase b(NOLOCK) ON b.PatientID = a.PatientID  " +
                         "WHERE DelDate IS NULL                                        " +
                         "AND ReportDate IS NOT NULL                                   " +
                         "AND OutPatientNum IS NOT NULL                                " +
                         "AND OutPatientNum <> ''                                      " +
                         "AND ReportDate >= @BeginDate AND ReportDate < @EndDate ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbCheckRecord>(connStrP, sql, parameters);
        }

        //门诊检验信息
        public List<MZTbAssayRecord> GetMZTbAssayRecords(string BeginDate,string EndDate)
        {
            string sql = "select '43120477700' as YLJGDM,                                                                                                                         " +
                         "       CONVERT(char(8),a.date_test,112) + a.YBH as JYBGID,                                                                                              " +
                         "       '云南省第三人民医院' AS YYMC,                                                                                                                      " +
                         "       a.Patient_Code as JZLSH,                                                                                                                         " +
                         "       '09' as GRBSLX,                                                                                                                                  " +
                         "       a.Patient_Code as GRBSH,                                                                                                                         " +
                         "       CASE a.patient_kind WHEN '1' THEN a.patient_kind                                                                                                 " +
                         "                           WHEN '2' THEN '1'                                                                                                            " +
                         "                           WHEN '0' THEN '2'                                                                                                            " +
                         "                           WHEN '8' THEN '2'                                                                                                            " +
                         "       ELSE '9'                                                                                                                                         " +
                         "       END as MZZYBZ,                                                                                                                                   " +
                         "       (case a.Patient_Kind  when '1' then a.Patient_Code when '2' then a.Patient_Code else NULL end) as MJZH,                                          " +
                         "       (case a.Patient_Kind  when '0' then a.Patient_Code when '8' then a.Patient_Code else NULL end)  as ZYH,                                          " +
                         "       CASE ISNULL(a.patient_kind,NULL) WHEN '1' THEN a.patient_kind                                                                                    " +
                         "                                      WHEN '2' THEN a.patient_kind                                                                                      " +
                         "                                      WHEN '0' THEN '3'                                                                                                 " +
                         "                                      WHEN '8' THEN '3'                                                                                                 " +
                         "       ELSE '4'                                                                                                                                         " +
                         "       END as HZLXDM,                                                                                                                                   " +
                         "       A.YBH AS JYBBH,                                                                                                                                  " +
                         "       d.Name as JYXMMC,                                                                                                                                " +
                         "       (select name from ut_dict_user where User_ID = A.reporter) as JYYSQM,                                                                            " +
                         "       CONVERT(VARCHAR(10),A.Date_Check,120) AS JYRQ,                                                                                                   " +
                         "       ISNULL((select Group_Name from UT_DICT_Group  where Group_Code =(select check_group from ut_dict_report  where code = LEFT(A.YBH,2))),'-') AS BGKSMC, " +
                         "       '-' AS JYBGJG,                                                                                                                                  " +
                         "       A.Kind AS BBDM,                                                                                                                                  " +
                         "       (select Kind_Name from UT_Check_Module_Kind where Kind_Code = a.kind ) AS BBMC,                                                                  " +
                         "       ISNULL((select check_group from ut_dict_report  where code = LEFT(A.YBH,2)),'9999') AS BGDLBBM,                                                  " +
                         "        (select report_name from ut_dict_report where code = LEFT(A.YBH,2)) AS BGDLB                                                                    " +
                         "from   ut_check_patient a(NOLOCK) left outer join ut_check_collection c (nolock) on a.date_test = c.date_test and a.ybh = c.ybh, UT_DICT_Project_Kind d " +
                         "WHERE  a.Flag_Send ='5' and c.Item_Check = d.Code                                                                                                       " +
                         "AND a.Collection_time >= @BeginDate AND a.Collection_time < @EndDate                                                                                    " +
                         "AND a.patient_kind IN ('1','2')                                                                                                                    ";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
            };
            return db.Context.QueryMany<MZTbAssayRecord>(connStrL, sql, parameters);
        }

        //门诊检验结果指标信息
        public List<MZTbAssayQuotaResult> GetMZTbAssayQuotaResults(string BeginDate,string EndDate)
        {
            string sql = "SELECT     '43120477700' as YLJGDM,                                                                                                                                                       " +
                         "           convert(char(8),r.test_date,112) + r.YBH AS JYZBLSH,                                                                                                                           " +
                         "           '云南省第三人民医院' AS YYMC,                                                                                                                                                    " +
                         "           convert(char(8),r.test_date,112) + r.YBH AS JYBGID,                                                                                                                            " +
                         "           convert(VARCHAR(10),r.test_date,120) as BGRQ,                                                                                                                                  " +
                         "           (select name from dbo.ut_dict_user where user_id = p.reporter) as JCRXM,                                                                                                       " +
                         "           r.item_code as YBSFDM,                                                                                                                                                         " +
                         "           'ZZZZZZZZZZZZZZZ' AS YZMXBMYB,                                                                                                                                                 " +
                         "CASE(select test_mothod_name from ut_dict_method where test_mothod in (select test_method from ut_dict_items where item_code = r.item_code)) WHEN '' THEN '未上传'                         " +
                         "           ELSE ISNULL((select test_mothod_name from ut_dict_method where test_mothod in (select test_method from ut_dict_items where item_code= r.item_code)),'未上传') END AS JCFFMC,    " +
                         "CASE(select Item_Name from dbo.UT_DICT_Items where Item_Code = r.Item_Code) WHEN '' THEN '未上传'                                                                                          " +
                         "           ELSE ISNULL((select Item_Name from dbo.UT_DICT_Items where Item_Code=r.Item_Code),'未上传') END AS JCZBMC,                                                                      " +
                         "            case (select Value_Type from dbo.UT_DICT_Items where Item_Code = r.Item_Code) when '1' then '1' when '2' then '2' when '3' then '2' when '4' then '2' else '9' end as ZBJGLX, " +
                         "           r.Test_Value2 as JCJGZB,                                                                                                                                                       " +
                         "           dbo.fun_share_midat(r.Text_range,1,'-') as CKSX,                                                                                                                               " +
                         "           dbo.fun_share_midat(r.Text_range,0,'-') as CKXX,                                                                                                                               " +
                         "           r.Test_Value2 AS DXCKJG,                                                                                                                                                        " +
                         "           '项' as JLDW                                                                                                                                                                    " +
                         "FROM       dbo.UT_Check_Result AS r,dbo.ut_check_patient as p                                                                                                                             " +
                         "WHERE      p.Date_test = r.Test_Date and p.YBH = r.YBH and p.flag_send = '5'                                                                                                              " +
                         "AND r.Test_Date >= @BeginDate AND r.Test_Date < @EndDate ";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            return db.Context.QueryMany<MZTbAssayQuotaResult>(connStrL, sql, parameters);
        }
    }
}
